package ru.vsu.cs.kis.elections.server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ru.vsu.cs.kis.elections.dto.CandidateDto;
import ru.vsu.cs.kis.elections.server.db.DBSchema;

public class CandidateDao {

	private Connection connection;

	public CandidateDao(Connection con) {
		this.connection = con;
	}

	/** parse request result into candidate's list */
	private ArrayList<CandidateDto> parseCandidates(ResultSet requestResult)
			throws SQLException {
		ArrayList<CandidateDto> result = null;
		if (requestResult == null) {
			return result;
		} else {
			result = new ArrayList<CandidateDto>();
		}
		CandidateDto candidate;
		while (requestResult.next()) {
			candidate = new CandidateDto(
					requestResult.getInt(DBSchema.DBT_CANDIDATE_ID),
					requestResult.getString(DBSchema.DBT_CANDIDATE_FIO),
					requestResult.getInt(3));
			result.add(candidate);
		}
		return result;
	}

	/** select all candidates from DB. All votes=0*/
	public ArrayList<CandidateDto> getCandidates(){
		String query = "select * from candidate";
		ArrayList<CandidateDto> result=new ArrayList<CandidateDto>();
		ResultSet rs = null;
		try {
			PreparedStatement p = connection.prepareStatement(query);
			rs = p.executeQuery();
			System.out.println("Request success.");
		} catch (SQLException e) {
			System.out.println("Request is failed.");
			e.printStackTrace();
		}
		try {
			CandidateDto candidate;
			while (rs.next()){
				candidate = new CandidateDto(
						rs.getInt(DBSchema.DBT_CANDIDATE_ID),
						rs.getString(DBSchema.DBT_CANDIDATE_FIO),
						0);
				result.add(candidate);
			}
			return result;
		} catch (SQLException e) {
			System.out.println("Selection is failed.");
			e.printStackTrace();
			return null;
		}		
	}
	
	/** select all candidates from DB. Votes is a sum on all precincts*/
	public ArrayList<CandidateDto> getCandAllVotes() {
		String query = "select candidate.id_cand, candidate.fio, sum(votes.votes) from candidate LEFT OUTER join votes on (candidate.id_cand=votes.id_cand) group by candidate.id_cand;";
		ResultSet rs = null;
		try {
			PreparedStatement p = connection.prepareStatement(query);
			rs = p.executeQuery();
			System.out.println("Request success.");
		} catch (SQLException e) {
			System.out.println("Request is failed.");
			e.printStackTrace();
		}
		try {
			return parseCandidates(rs);
		} catch (SQLException e) {
			System.out.println("Selection is failed.");
			e.printStackTrace();
			return null;
		}
	}

	/** select all candidates from DB. With votes on current precinct*/
	public ArrayList<CandidateDto> getCandLocalVotes(Integer idPrecinct) {
		String query = "select candidate.id_cand, candidate.fio, sum(votes.votes) from candidate LEFT OUTER join votes on (candidate.id_cand=votes.id_cand) where votes.id_prec=? group by candidate.id_cand;";
		ResultSet rs = null;
		try {
			PreparedStatement p = connection.prepareStatement(query);
			p.setInt(1, idPrecinct);
			rs = p.executeQuery();
			System.out.println("Request success.");
		} catch (SQLException e) {
			System.out.println("Request is failed.");
			e.printStackTrace();
		}
		try {
			return parseCandidates(rs);
		} catch (SQLException e) {
			System.out.println("Selection is failed.");
			e.printStackTrace();
			return null;
		}
	}

	/** insert votes into table.  */
	public boolean updateVote(CandidateDto candidate, Integer idPrecinct) {
		String query = "insert into votes (id_cand,id_prec,votes) values (?,?,?) ON DUPLICATE KEY UPDATE votes=votes+?;";
		try {
			PreparedStatement p = connection.prepareStatement(query);
			p.setInt(1,candidate.getId());
			p.setInt(2,idPrecinct);
			p.setInt(3, candidate.getVotes());
			p.setInt(4, candidate.getVotes());			
			int res = p.executeUpdate();
			if (res > 0) {
				System.out.println("Insert votes success.");
				return true;
			}
		} catch (SQLException e) {
			System.out.println("Insert votes is failed.");
			e.printStackTrace();
			return false;
		}
		return false;
	}
}
